You can do all sorts of administrative tasks with QlikView applications through Automation API. Export charts to excel or csv files, export variables from application, set sheet level security parameters before deploying application or warm your application on server by automatically opening some sheets and selecting some most popular values in listboxes.
In most cases I would code such a task not as a macro inside QlikView application but as an separate external VBSCript file. Benefits of that approach:
Several tips for using external VBScript scripts:
Most examples of QlikView automation on the net and all examples in APIGuide are for usage in macros. There is two simple steps for converting such samples into working VBscript:
In QlikView macros global variable ActiveDocument is entry point to automation API, QlikView application object accessible as ActiveDocument.GetApplication
In VBScript we initialize two automation objects for qlikview app and qlikview document.
Set MyApp = CreateObject("QlikTech.QlikView")
Set MyDoc = MyApp.OpenDoc ("C:\QlikViewApps\Demo.qvw")
Note that you should use absolute path to QlikView document on OpenDoc parameter. That is mandatory.
Then use MyDoc instead of global variable ActiveDocument and MyApp instead of ActiveDocument.GetApplication
For example that could be usefull to schedule warming up of your application after nightly reload. To automate application residing on the QlikView server you shoud use full path to your application on OpenDoc parameter. It could be something like:
Set MyDoc = MyApp.OpenDoc ("qvp://localhost/AppFolder/My application.qvw")
For me it works only with ActiveDirectory authentication. Basically if you can open application in QlikView Desktop with Use NT Identity radiobutton selected and user/login dialog do not appear on opening - automation from VBScript should work too.
It could be useful to collect common utility functions and classes (yes, VBScript have a classes too!!!) in a some library and use it throughout many scripts. Unfortunately VBScrip lacks standard import directive.
WSF file format add it and much more. I’ve tried it but it feels like an unnecessary complicated stuff.
So if import-like functionality is truly necessary I would use simple one-line function includeFile like in this example where on top of our script we import code from QvUtils.vbs
Sub includeFile(ByVal fSpec)
executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile(fSpec).readAll()
End Sub
includeFile "QvUtils.vbs"
...
Below some simple samples for illustration:
Given simple test application:
Script to export straight chart to excel file with different values selected in field Year
set fso = CreateObject("Scripting.FileSystemObject")
dim CurrentDirectory
CurrentDirectory = fso.GetParentFolderName(Wscript.ScriptFullName)
set qv = CreateObject("QlikTech.QlikView")
dim qvDocName
qvDocName = fso.BuildPath(CurrentDirectory, "..\App\AutomationTest.qvw")
set doc = qv.OpenDoc(qvDocName)
set chart = doc.GetSheetObject("CH01")
doc.Fields("Year").Clear
set yearValues=doc.Fields("Year").GetPossibleValues
dim curVal
for i=0 to yearValues.Count-1
curVal = yearValues.Item(i).Text
doc.Fields("Year").Select curVal
chart.ExportBiff(fso.BuildPath(CurrentDirectory,"..\Output\Report_" & curVal & ".xls"))
next
doc.CloseDoc
qv.Quit
Same script separated to utils mini-library and script proper:
QvUtils.vbs:
function GetAbsolutePath(ByVal filePath)
if Mid(filePath,2,1) = ":" OR Left(filePath,2) = "\\" then 'Absolute path in input parameter'
GetAbsolutePath = filePath
else
dim fso: set fso = CreateObject("Scripting.FileSystemObject")
GetAbsolutePath = fso.BuildPath(fso.GetParentFolderName(Wscript.ScriptFullName), filePath)
end if
end function
Class QlikView
Private m_App
Private m_Doc
Private m_docName
Private Sub Class_Initialize
m_docName = ""
End Sub
Public Property Get app
set app = m_App
End Property
Public Property Get doc
set doc = m_Doc
End Property
Public Property Get docName
docName = m_docName
End Property
public function setDocument(ByVal docName)
m_docName = GetAbsolutePath(docName)
end function
Public Function open(ByVal docName)
setDocument(docName)
set m_App = CreateObject("QlikTech.QlikView")
set m_Doc = app.OpenDoc(m_docName)
End Function
Public function Quit
m_App.Quit
Release
End function
Public function Release
set m_shell = Nothing
set m_Doc = Nothing
set mApp = Nothing
end function
End Class
GenerateReports.vbs:
Sub includeFile(ByVal fSpec)
executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile(fSpec).readAll()
End Sub
includeFile "QvUtils.vbs"
with New QlikView
.open("..\App\AutomationTest.qvw")
set chart = .doc.GetSheetObject("CH01")
.doc.Fields("Year").Clear
set yearValues = .doc.Fields("Year").GetPossibleValues()
dim curVal
for i=0 to yearValues.Count - 1
curVal = yearValues.Item(i).Text
.doc.Fields("Year").Select curVal
chart.ExportBiff(GetAbsolutePath("..\Output\Report_" & curVal & ".xls"))
next
.doc.CloseDoc
.Quit
end with
Script to disable most settings in Sheet Security dialog (can be run upon qvw before deployment) Target configuration is like this:
Script uses QvUtils.vbs
Use as in cscript set_sheet_properties.vbs ..\App\AutomationTest.qvw
set_sheet_permissions.vbs
Sub includeFile(ByVal fSpec)
executeGlobal CreateObject("Scripting.FileSystemObject").openTextFile(fSpec).readAll()
End Sub
includeFile "QvUtils.vbs"
if WScript.Arguments.Count <> 1 then
WScript.Echo "Syntax is: cscript SetSheetPermissions.vbs <QlikViewFileName>"
WScript.Quit 1
end if
with New QlikView
.open(WScript.Arguments(0))
for i = 0 to .doc.NoOfSheets - 1
set sheet = .doc.GetSheet(i)
set sp=sheet.GetProperties
sp.UserPermissions.CopyCloneSheetObject = false
sp.UserPermissions.AccessSheetProperties = false
sp.UserPermissions.AddSheetObject = false
sp.UserPermissions.MoveSizeSheetObject = false
sp.UserPermissions.RemoveSheet = false
sp.UserPermissions.RemoveSheetObject = false
sheet.SetProperties sp
next
.doc.Save
.Quit
end with